{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fb2f92e1-af66-4927-935c-50de0c2d0da6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus'] = False # 用来正常显示负号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ad2c45af-6b2a-446f-b881-43e714308c62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>调度安排ID</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>站点信息ID</th>\n",
       "      <th>客户下单ID</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>到站吨位</th>\n",
       "      <th>单价</th>\n",
       "      <th>货款</th>\n",
       "      <th>车辆信息</th>\n",
       "      <th>收货时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>3624</td>\n",
       "      <td>218</td>\n",
       "      <td>319</td>\n",
       "      <td>1978</td>\n",
       "      <td>103</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>314</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>3637</td>\n",
       "      <td>107</td>\n",
       "      <td>93</td>\n",
       "      <td>1981</td>\n",
       "      <td>124</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>15490.40</td>\n",
       "      <td>241</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>3638</td>\n",
       "      <td>152</td>\n",
       "      <td>191</td>\n",
       "      <td>1986</td>\n",
       "      <td>121</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>43.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>14028.80</td>\n",
       "      <td>276</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>3639</td>\n",
       "      <td>152</td>\n",
       "      <td>191</td>\n",
       "      <td>1986</td>\n",
       "      <td>121</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>52.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>16704.00</td>\n",
       "      <td>257</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>3666</td>\n",
       "      <td>139</td>\n",
       "      <td>141</td>\n",
       "      <td>1998</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>32.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>9437.44</td>\n",
       "      <td>163</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>62734</td>\n",
       "      <td>461</td>\n",
       "      <td>744</td>\n",
       "      <td>32869</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>36.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1800.00</td>\n",
       "      <td>1345</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>62747</td>\n",
       "      <td>609</td>\n",
       "      <td>1145</td>\n",
       "      <td>32886</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>41.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>6379.80</td>\n",
       "      <td>106</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>62748</td>\n",
       "      <td>103</td>\n",
       "      <td>87</td>\n",
       "      <td>32893</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>34.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>5363.00</td>\n",
       "      <td>226</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>62745</td>\n",
       "      <td>201</td>\n",
       "      <td>892</td>\n",
       "      <td>32890</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>40.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>6908.80</td>\n",
       "      <td>125</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>62744</td>\n",
       "      <td>388</td>\n",
       "      <td>1278</td>\n",
       "      <td>32911</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>766</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID  调度安排ID  客户信息ID  站点信息ID  客户下单ID  发货地信息ID  货品类型  产品规格  产品品牌  \\\n",
       "0       2509    3624     218     319    1978      103     1     1     8   \n",
       "1       2510    3637     107      93    1981      124     0     0     0   \n",
       "2       2514    3638     152     191    1986      121     0     0     0   \n",
       "3       2515    3639     152     191    1986      121     0     0     0   \n",
       "4       2516    3666     139     141    1998      104     1     1     9   \n",
       "...      ...     ...     ...     ...     ...      ...   ...   ...   ...   \n",
       "49870  60712   62734     461     744   32869      104     1     1     9   \n",
       "49871  60713   62747     609    1145   32886      105     1     1     9   \n",
       "49872  60714   62748     103      87   32893      104     1     1     9   \n",
       "49873  60715   62745     201     892   32890      104     1     1     9   \n",
       "49874  60716   62744     388    1278   32911      105     1     1     9   \n",
       "\n",
       "        发货吨位  到站吨位     单价        货款  车辆信息                 收货时间  \n",
       "0      38.38   0.0  207.0   7944.66   314  2022-07-21 07:10:48  \n",
       "1      45.56   0.0  340.0  15490.40   241  2022-07-21 07:19:48  \n",
       "2      43.84   0.0  320.0  14028.80   276  2022-07-21 10:49:34  \n",
       "3      52.20   0.0  320.0  16704.00   257  2022-07-21 10:49:35  \n",
       "4      32.32   0.0  292.0   9437.44   163  2022-07-21 11:23:31  \n",
       "...      ...   ...    ...       ...   ...                  ...  \n",
       "49870  36.00   0.0   50.0   1800.00  1345  2024-06-24 13:28:49  \n",
       "49871  41.16   0.0  155.0   6379.80   106  2024-06-24 14:08:54  \n",
       "49872  34.60   0.0  155.0   5363.00   226  2024-06-24 14:09:20  \n",
       "49873  40.64   0.0  170.0   6908.80   125  2024-06-24 14:40:16  \n",
       "49874  35.68   0.0  150.0   5352.00   766  2024-06-24 15:07:41  \n",
       "\n",
       "[49875 rows x 15 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.read_csv(\"ERP_FHJL.csv\")\n",
    "df1.columns=[\"ID\",\"调度安排ID\",\"客户信息ID\",\"站点信息ID\",\"客户下单ID\",\"发货地信息ID\",\"货品类型\",\"产品规格\",\"产品品牌\",\"发货吨位\",\"到站吨位\",\"单价\",\"货款\",\"车辆信息\",\"收货时间\"]\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4952ef4b-46db-4ba9-9cbb-ab6bcd6c73f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>站点ID</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>运货方式</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>发货地ID</th>\n",
       "      <th>到站时间</th>\n",
       "      <th>销售人员</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>79</td>\n",
       "      <td>37</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-13 15:59:10</td>\n",
       "      <td>xssb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>745</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-29 05:46:42</td>\n",
       "      <td>DS0403001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>865</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-01 04:30:19</td>\n",
       "      <td>DS0403001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>928</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-02 10:46:07</td>\n",
       "      <td>DS0403001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>958</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-03 04:27:59</td>\n",
       "      <td>DS0403001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27948</th>\n",
       "      <td>32911</td>\n",
       "      <td>388</td>\n",
       "      <td>1278</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27949</th>\n",
       "      <td>32912</td>\n",
       "      <td>636</td>\n",
       "      <td>1211</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27950</th>\n",
       "      <td>32913</td>\n",
       "      <td>497</td>\n",
       "      <td>1274</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>42.36</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-05 08:05:00</td>\n",
       "      <td>chediao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27951</th>\n",
       "      <td>32914</td>\n",
       "      <td>479</td>\n",
       "      <td>793</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0402003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27952</th>\n",
       "      <td>32915</td>\n",
       "      <td>546</td>\n",
       "      <td>979</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>105</td>\n",
       "      <td>2024-06-25 08:05:00</td>\n",
       "      <td>DS0403000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27953 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID  客户信息ID  站点ID  货品类型  产品规格  产品品牌    发货吨位  运货方式         业务类型描述  \\\n",
       "0         79      37    46     0     0     0  100.00     1  配送 (贸易+起驳+运输)   \n",
       "1        745     141   276     0     0     0  100.00     1  配送 (贸易+起驳+运输)   \n",
       "2        865     141   276     0     0     0  200.00     1  配送 (贸易+起驳+运输)   \n",
       "3        928     141   276     0     0     0  200.00     1  配送 (贸易+起驳+运输)   \n",
       "4        958     141   276     0     0     0  150.00     1  配送 (贸易+起驳+运输)   \n",
       "...      ...     ...   ...   ...   ...   ...     ...   ...            ...   \n",
       "27948  32911     388  1278     1     1     9   35.00     0        自提 (贸易)   \n",
       "27949  32912     636  1211     1     1     9   35.00     0        自提 (贸易)   \n",
       "27950  32913     497  1274     1     1     9   42.36     0        自提 (贸易)   \n",
       "27951  32914     479   793     1     1     9   30.00     0        自提 (贸易)   \n",
       "27952  32915     546   979     1     1     9   30.00     0     配送 (贸易+运输)   \n",
       "\n",
       "       发货地ID                 到站时间       销售人员  \n",
       "0        121  2022-06-13 15:59:10       xssb  \n",
       "1        121  2022-06-29 05:46:42  DS0403001  \n",
       "2        121  2022-07-01 04:30:19  DS0403001  \n",
       "3        121  2022-07-02 10:46:07  DS0403001  \n",
       "4        121  2022-07-03 04:27:59  DS0403001  \n",
       "...      ...                  ...        ...  \n",
       "27948    104  2024-06-24 08:05:00  DS0403003  \n",
       "27949    104  2024-06-24 08:05:00  DS0403003  \n",
       "27950    104  2024-06-05 08:05:00    chediao  \n",
       "27951    104  2024-06-24 08:05:00  DS0402003  \n",
       "27952    105  2024-06-25 08:05:00  DS0403000  \n",
       "\n",
       "[27953 rows x 12 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2=pd.read_csv(\"ERP_KHXD.csv\")\n",
    "df2.columns=[\"ID\",\"客户信息ID\",\"站点ID\",\"货品类型\",\"产品规格\",\"产品品牌\",\"发货吨位\",\"运货方式\",\"业务类型描述\",\"发货地ID\",\"到站时间\",\"销售人员\"]\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "96fa81a9-cf09-4b9e-b7c2-22fb9a1c3093",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n"
     ]
    }
   ],
   "source": [
    "has_duplicates = df2.duplicated().any()  \n",
    "print(has_duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "92ccf214-589d-42b0-b31b-2792b5da9e3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.DataFrame(df1[\"ID\"])\n",
    "df[\"收货时间\"]=df1[\"收货时间\"]\n",
    "df[\"客户信息ID\"]=df1[\"客户信息ID\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "378e5c61-650a-41ef-95f4-29754f5e7118",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID\n",
       "0       2509  2022-07-21 07:10:48     218\n",
       "1       2510  2022-07-21 07:19:48     107\n",
       "2       2514  2022-07-21 10:49:34     152\n",
       "3       2515  2022-07-21 10:49:35     152\n",
       "4       2516  2022-07-21 11:23:31     139\n",
       "...      ...                  ...     ...\n",
       "49870  60712  2024-06-24 13:28:49     461\n",
       "49871  60713  2024-06-24 14:08:54     609\n",
       "49872  60714  2024-06-24 14:09:20     103\n",
       "49873  60715  2024-06-24 14:40:16     201\n",
       "49874  60716  2024-06-24 15:07:41     388\n",
       "\n",
       "[49875 rows x 3 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4ed8a9a6-ca4b-4326-98a8-9862006ac1d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(df, df2[['客户信息ID','销售人员']], on='客户信息ID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "c872661a-0376-46d3-8725-997047c9a4e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900304</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900305</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900306</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900307</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900308</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24900309 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID                 收货时间  客户信息ID       销售人员\n",
       "0          2509  2022-07-21 07:10:48     218  DS0401002\n",
       "1          2509  2022-07-21 07:10:48     218  DS0401002\n",
       "2          2509  2022-07-21 07:10:48     218  DS0401002\n",
       "3          2509  2022-07-21 07:10:48     218  DS0401002\n",
       "4          2509  2022-07-21 07:10:48     218  DS0401002\n",
       "...         ...                  ...     ...        ...\n",
       "24900304  60716  2024-06-24 15:07:41     388  DS0403003\n",
       "24900305  60716  2024-06-24 15:07:41     388  DS0403003\n",
       "24900306  60716  2024-06-24 15:07:41     388  DS0403003\n",
       "24900307  60716  2024-06-24 15:07:41     388  DS0403003\n",
       "24900308  60716  2024-06-24 15:07:41     388  DS0403003\n",
       "\n",
       "[24900309 rows x 4 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "e2cdcd6d-0c4c-424e-bb14-25a20c337948",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = merged_df.drop_duplicates(subset=['ID']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "9303956a-8821-4164-b4d3-4345683de866",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>775</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1024</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24899252</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24899568</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24899668</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900120</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900299</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID                 收货时间  客户信息ID       销售人员\n",
       "0          2509  2022-07-21 07:10:48     218  DS0401002\n",
       "147        2510  2022-07-21 07:19:48     107  DS0404003\n",
       "526        2514  2022-07-21 10:49:34     152  DS0402001\n",
       "775        2515  2022-07-21 10:49:35     152  DS0402001\n",
       "1024       2516  2022-07-21 11:23:31     139  DS0402003\n",
       "...         ...                  ...     ...        ...\n",
       "24899252  60712  2024-06-24 13:28:49     461  DS0401002\n",
       "24899568  60713  2024-06-24 14:08:54     609    chediao\n",
       "24899668  60714  2024-06-24 14:09:20     103  DS0403002\n",
       "24900120  60715  2024-06-24 14:40:16     201  DS0402003\n",
       "24900299  60716  2024-06-24 15:07:41     388  DS0403003\n",
       "\n",
       "[49875 rows x 4 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2b27b7ce-c428-4e7c-954f-56b0795625d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3=pd.read_csv(\"sys_user.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "4b0b1abd-568c-4add-9352-68e6100dc38e",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>销售员名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>admin</td>\n",
       "      <td>系统管理员1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>dsadmin</td>\n",
       "      <td>管理员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>zhangsan</td>\n",
       "      <td>张三</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>testuser</td>\n",
       "      <td>testuser</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>xs01</td>\n",
       "      <td>销售一</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>149</td>\n",
       "      <td>DS02005</td>\n",
       "      <td>袁义平</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>150</td>\n",
       "      <td>JCHS</td>\n",
       "      <td>荆楚宏盛</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>151</td>\n",
       "      <td>DS06004</td>\n",
       "      <td>高</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>152</td>\n",
       "      <td>测试客服</td>\n",
       "      <td>测试客服</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>154</td>\n",
       "      <td>testkf</td>\n",
       "      <td>testkf</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>150 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id      销售人员     销售员名称\n",
       "0          1     admin    系统管理员1\n",
       "1          4   dsadmin       管理员\n",
       "2          5  zhangsan        张三\n",
       "3          7  testuser  testuser\n",
       "4          8      xs01       销售一\n",
       "..       ...       ...       ...\n",
       "145      149   DS02005       袁义平\n",
       "146      150      JCHS      荆楚宏盛\n",
       "147      151   DS06004         高\n",
       "148      152      测试客服      测试客服\n",
       "149      154    testkf    testkf\n",
       "\n",
       "[150 rows x 3 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.columns=[\"user_id\",\"销售人员\",\"销售员名称\"]\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "8adbc059-6652-43da-b3c8-9a9a0a556fe5",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(df, df3[['销售人员','销售员名称']], on='销售人员', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c799f785-51b6-4a1f-a3c8-41bc1bc13db5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>销售员名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>钱多多</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>张蕊</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>肖翔</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>肖翔</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>甄常</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>钱多多</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>调度室</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>谭志强</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>甄常</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>史丹</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员 销售员名称\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002   钱多多\n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003    张蕊\n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001    肖翔\n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001    肖翔\n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003    甄常\n",
       "...      ...                  ...     ...        ...   ...\n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002   钱多多\n",
       "49871  60713  2024-06-24 14:08:54     609    chediao   调度室\n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002   谭志强\n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003    甄常\n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003    史丹\n",
       "\n",
       "[49875 rows x 5 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "b8e1d8a5-3462-4b3b-b88a-c08d3bb8b6a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df4=pd.read_csv(\"sys_dict_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "7f1c8591-5d74-47ef-a55d-ae4e7bc67c73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>dict_type</th>\n",
       "      <th>dict_value</th>\n",
       "      <th>dict_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>0</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>2</td>\n",
       "      <td>未知</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>sys_show_hide</td>\n",
       "      <td>0</td>\n",
       "      <td>显示</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>sys_show_hide</td>\n",
       "      <td>1</td>\n",
       "      <td>隐藏</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>745</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>746</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>1</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>747</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>2</td>\n",
       "      <td>V800(袋装)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>748</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>3</td>\n",
       "      <td>V800(散装)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>749</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>4</td>\n",
       "      <td>PO52.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>266 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id      dict_type dict_value dict_label\n",
       "0      1   sys_user_sex          0          男\n",
       "1      2   sys_user_sex          1          女\n",
       "2      3   sys_user_sex          2         未知\n",
       "3      4  sys_show_hide          0         显示\n",
       "4      5  sys_show_hide          1         隐藏\n",
       "..   ...            ...        ...        ...\n",
       "261  745       ERP_CPGG          0    P.O42.5\n",
       "262  746       ERP_CPGG          1        S95\n",
       "263  747       ERP_CPGG          2   V800(袋装)\n",
       "264  748       ERP_CPGG          3   V800(散装)\n",
       "265  749       ERP_CPGG          4     PO52.5\n",
       "\n",
       "[266 rows x 4 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "b816b3b5-0068-4629-bcc2-212924d2a6db",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(df, df2[['客户信息ID','业务类型描述']], on='客户信息ID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "b5995bc0-86dc-4024-939b-55fe426938d0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>775</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1024</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24899252</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24899568</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24899668</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900120</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24900299</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID                 收货时间  客户信息ID       销售人员         业务类型描述\n",
       "0          2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)\n",
       "147        2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)\n",
       "526        2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)\n",
       "775        2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)\n",
       "1024       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)\n",
       "...         ...                  ...     ...        ...            ...\n",
       "24899252  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)\n",
       "24899568  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)\n",
       "24899668  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)\n",
       "24900120  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)\n",
       "24900299  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)\n",
       "\n",
       "[49875 rows x 5 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = merged_df.drop_duplicates(subset=['ID']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "bcccd2e1-a249-4459-bbd7-15254de751ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>销售人员</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "      <td>测试一下</td>\n",
       "      <td>DS04001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>武汉昊恒(水泥)</td>\n",
       "      <td>DS0406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>华安水泥(君善建)</td>\n",
       "      <td>DS0406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>合力久盛(现金)</td>\n",
       "      <td>DS0404001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532</th>\n",
       "      <td>646</td>\n",
       "      <td>新中环</td>\n",
       "      <td>DS0403000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>533</th>\n",
       "      <td>647</td>\n",
       "      <td>湖北联发水泥厂</td>\n",
       "      <td>DS0501003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>648</td>\n",
       "      <td>武汉宏新途远商贸有限公司</td>\n",
       "      <td>DS0405003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>649</td>\n",
       "      <td>慧迪（授信）</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536</th>\n",
       "      <td>650</td>\n",
       "      <td>华安水泥东湖分公司</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>537 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id          客户名称       销售人员\n",
       "0     16          测试一下    DS04001\n",
       "1     18          武汉中村  DS0403003\n",
       "2     20      武汉昊恒(水泥)  DS0406000\n",
       "3     21     华安水泥(君善建)  DS0406000\n",
       "4     23      合力久盛(现金)  DS0404001\n",
       "..   ...           ...        ...\n",
       "532  646           新中环  DS0403000\n",
       "533  647       湖北联发水泥厂  DS0501003\n",
       "534  648  武汉宏新途远商贸有限公司  DS0405003\n",
       "535  649        慧迪（授信）  DS0401002\n",
       "536  650     华安水泥东湖分公司  DS0403003\n",
       "\n",
       "[537 rows x 3 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5=pd.read_csv(\"ERP_KHXX.csv\")\n",
    "df5.columns=[\"id\",'客户名称','销售人员']\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "32579e47-7981-41a5-b5b7-1beed4e16c82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607220</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607253</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607268</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607269</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607315</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID                 收货时间  客户信息ID       销售人员         业务类型描述  \\\n",
       "0         2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)   \n",
       "33        2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)   \n",
       "86        2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)   \n",
       "97        2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)   \n",
       "108       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)   \n",
       "...        ...                  ...     ...        ...            ...   \n",
       "1607220  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)   \n",
       "1607253  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)   \n",
       "1607268  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)   \n",
       "1607269  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)   \n",
       "1607315  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)   \n",
       "\n",
       "             客户名称  \n",
       "0        建工-水泥-恒泰  \n",
       "33         湖北加瓦商贸  \n",
       "86          九欣通建材  \n",
       "97          九欣通建材  \n",
       "108          正洪岩土  \n",
       "...           ...  \n",
       "1607220  建工-水泥-恒泰  \n",
       "1607253    德旺码头仓库  \n",
       "1607268       NaN  \n",
       "1607269      正洪岩土  \n",
       "1607315      武汉中村  \n",
       "\n",
       "[49875 rows x 6 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = pd.merge(df, df5[['销售人员','客户名称']], on='销售人员', how='left')\n",
    "df = merged_df.drop_duplicates(subset=['ID']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "cba9b281-57c7-48a1-b60c-1b7e9889cd7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"df.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "29ad85d5-5a74-4426-8692-0872343e3987",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel(\"df.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "891ea552-47cc-4114-a417-b99dbe87bb67",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰\n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸\n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材\n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材\n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土\n",
       "...      ...                  ...     ...        ...            ...       ...\n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰\n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库\n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN\n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土\n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村\n",
       "\n",
       "[49875 rows x 6 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "947ef593-13a3-4b0e-b82a-140d9ab930b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['发货地信息ID']=df1['发货地信息ID']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "015b8bde-237a-47e2-baa6-66aee834d206",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID  \n",
       "0          103  \n",
       "1          124  \n",
       "2          121  \n",
       "3          121  \n",
       "4          104  \n",
       "...        ...  \n",
       "49870      104  \n",
       "49871      105  \n",
       "49872      104  \n",
       "49873      104  \n",
       "49874      105  \n",
       "\n",
       "[49875 rows x 7 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "11af4903-aa15-4111-a701-1e05c2a36162",
   "metadata": {},
   "outputs": [],
   "source": [
    "df6=pd.read_csv(\"ERP_FHDXX.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "ad611c37-817d-4843-aaf7-4f6df23a8723",
   "metadata": {},
   "outputs": [],
   "source": [
    "df6.columns=[\"发货地信息ID\",\"发货地类型\",\"发货地名称\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "f9eb047a-a122-4e8a-bbf2-9bde98b13c9d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称  \n",
       "0          103   金盛兰  \n",
       "1          124  德旺码头  \n",
       "2          121  江南码头  \n",
       "3          121  江南码头  \n",
       "4          104  武新一期  \n",
       "...        ...   ...  \n",
       "49870      104  武新一期  \n",
       "49871      105  武新二期  \n",
       "49872      104  武新一期  \n",
       "49873      104  武新一期  \n",
       "49874      105  武新二期  \n",
       "\n",
       "[49875 rows x 8 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = pd.merge(df, df6[[\"发货地信息ID\",\"发货地名称\"]], on=\"发货地信息ID\", how='left')\n",
    "df = merged_df.drop_duplicates(subset=['ID']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "9050a313-039c-4799-9587-5f5dd04e8e98",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['货品类型ID']=df1['货品类型']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "60b6f4d0-9064-44c9-a318-3194c9e1795a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称  货品类型ID  \n",
       "0          103   金盛兰       1  \n",
       "1          124  德旺码头       0  \n",
       "2          121  江南码头       0  \n",
       "3          121  江南码头       0  \n",
       "4          104  武新一期       1  \n",
       "...        ...   ...     ...  \n",
       "49870      104  武新一期       1  \n",
       "49871      105  武新二期       1  \n",
       "49872      104  武新一期       1  \n",
       "49873      104  武新一期       1  \n",
       "49874      105  武新二期       1  \n",
       "\n",
       "[49875 rows x 9 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "c29531fe-e2f5-43c8-ac48-155adaba78fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>dict_type</th>\n",
       "      <th>dict_value</th>\n",
       "      <th>dict_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>0</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>2</td>\n",
       "      <td>未知</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>sys_show_hide</td>\n",
       "      <td>0</td>\n",
       "      <td>显示</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>sys_show_hide</td>\n",
       "      <td>1</td>\n",
       "      <td>隐藏</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>745</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>746</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>1</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>747</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>2</td>\n",
       "      <td>V800(袋装)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>748</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>3</td>\n",
       "      <td>V800(散装)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>749</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>4</td>\n",
       "      <td>PO52.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>266 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id      dict_type dict_value dict_label\n",
       "0      1   sys_user_sex          0          男\n",
       "1      2   sys_user_sex          1          女\n",
       "2      3   sys_user_sex          2         未知\n",
       "3      4  sys_show_hide          0         显示\n",
       "4      5  sys_show_hide          1         隐藏\n",
       "..   ...            ...        ...        ...\n",
       "261  745       ERP_CPGG          0    P.O42.5\n",
       "262  746       ERP_CPGG          1        S95\n",
       "263  747       ERP_CPGG          2   V800(袋装)\n",
       "264  748       ERP_CPGG          3   V800(散装)\n",
       "265  749       ERP_CPGG          4     PO52.5\n",
       "\n",
       "[266 rows x 4 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "7b1a4b30-bec4-43da-9b45-dcda6f4a3c07",
   "metadata": {},
   "outputs": [],
   "source": [
    "hplx=df4.loc[77:78,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "66e29fea-b02f-4e9f-94c5-53247b7df5c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "hplx.columns=[\"ID\",\"dict_type\",\"货品类型ID\",\"货品类型\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "088567aa-d779-48d1-945e-99b1ff818b97",
   "metadata": {},
   "outputs": [],
   "source": [
    "hplx.to_excel('hplx.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "1bf01298-f3e8-4a4f-bbe1-bd5631189981",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"df.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "0e0aee1b-8cd9-4c43-af27-ff1e57344a19",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>dict_type</th>\n",
       "      <th>dict_value</th>\n",
       "      <th>dict_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>667</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>0</td>\n",
       "      <td>海螺水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>668</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>1</td>\n",
       "      <td>东方希望水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>669</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>2</td>\n",
       "      <td>三峡水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>670</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>3</td>\n",
       "      <td>京兰水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>671</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>4</td>\n",
       "      <td>四棵松水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>672</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>5</td>\n",
       "      <td>娲石水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>673</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>6</td>\n",
       "      <td>福兴水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>674</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>7</td>\n",
       "      <td>华新水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>675</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>8</td>\n",
       "      <td>金盛兰矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>676</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>9</td>\n",
       "      <td>武新矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>677</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>10</td>\n",
       "      <td>鑫缘矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>678</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>11</td>\n",
       "      <td>钢华矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>679</td>\n",
       "      <td>ERP_CPPP</td>\n",
       "      <td>12</td>\n",
       "      <td>嘉华矿粉</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id dict_type dict_value dict_label\n",
       "215  667  ERP_CPPP          0       海螺水泥\n",
       "216  668  ERP_CPPP          1     东方希望水泥\n",
       "217  669  ERP_CPPP          2       三峡水泥\n",
       "218  670  ERP_CPPP          3       京兰水泥\n",
       "219  671  ERP_CPPP          4      四棵松水泥\n",
       "220  672  ERP_CPPP          5       娲石水泥\n",
       "221  673  ERP_CPPP          6       福兴水泥\n",
       "222  674  ERP_CPPP          7       华新水泥\n",
       "223  675  ERP_CPPP          8      金盛兰矿粉\n",
       "224  676  ERP_CPPP          9       武新矿粉\n",
       "225  677  ERP_CPPP         10       鑫缘矿粉\n",
       "226  678  ERP_CPPP         11       钢华矿粉\n",
       "227  679  ERP_CPPP         12       嘉华矿粉"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.iloc[215:228]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "f19b60b3-a690-4807-b0f2-b432c0a2e38e",
   "metadata": {},
   "outputs": [],
   "source": [
    "cppp=df4.iloc[215:228]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "0d149f90-41bc-41d0-a212-f43ef3c2cb9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "cppp.to_excel('cppp.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92fceaa1-b73a-4b2b-ac74-cfcee9958ab3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
